<?php require_once('Connections/db_conn.php'); ?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
	"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<?php
	mysql_select_db($database_db_conn, $db_conn);
	$layout = explode('&&&', file_get_contents('layout.html'));
	echo $layout[0];

	session_start();
	if (!(isset($_SESSION['username'])))
		header("location:login.php");

?>

<html>
<body>

<!-- Begin Main Column -->

<div id="mainContent">
<?php


	//Gather Form Information
	if (isset($_POST["SearchFor"]) == True)
		$SearchedFor=$_POST["SearchFor"];
	else
		$SearchedFor="";
	if (isset($_POST["FirstName"]) == True)
		$FName=$_POST["FirstName"];
	else
		$FName="";
	if (isset($_POST["LastName"]) == True)
		$LName=$_POST["LastName"];
	else
		$FName="";
	if (isset($_POST["City"]) == True)
		$TheCity=$_POST["City"];
	else
		$TheCity="";
	if (isset($_POST["ParticipantType"]) == True)
		$PartType=$_POST["ParticipantType"];
	else
		$TheCity="";
	if (isset($_POST["NumberOfChildren"]) == True)
		$NoChildren=$_POST["NumberOfChildren"];
	else
		$NoChildren="";
	if (isset($_POST["pLanguage"]) == True)
		$Languages=$_POST["pLanguage"];
	else
		$Languages="";
	if (isset($_POST["Newsletter"]) == True)
		$Newsletter=$_POST["Newsletter"];
	else
		$Newsletter="";
	if (isset($_POST["Membership"]) == True)
		$Membership=$_POST["Membership"];
	else
		$Membership="";
	if (isset($_POST["StartDate"]) == True)
		$StartDate=$_POST["StartDate"];
	else
		$StartDate="";
	if (isset($_POST["EndDate"]) == True)
		$EndDate=$_POST["EndDate"];
	else
		$EndDate="";
	if (isset($_POST["WorkshopTopic"]) == True)
		$WorkshopTopic=$_POST["WorkshopTopic"];
	else
		$WorkshopTopic="";
	if (isset($_POST["PresenterFirst"]) == True)
		$PresenterFirst=$_POST["PresenterFirst"];
	else
		$PresenterFirst="";
	if (isset($_POST["PresenterLast"]) == True)
		$PresenterLast=$_POST["PresenterLast"];
	else
		$PresenterLast="";
	if (isset($_POST["Location"]) == True)
		$Location=$_POST["Location"];
	else
		$Location="";
	if (isset($_POST["WorkshopLanguage"]) == True)
		$WorkshopLanguage=$_POST["WorkshopLanguage"];
	else
		$WorkshopLanguage="";
	if (isset($_POST["CommunityPartner"]) == True)
		$CommunityPartner=$_POST["CommunityPartner"];
	else
		$CommunityPartner="";
	if (isset($_POST["OrganizationName"]) == True)
		$OrganizationName=$_POST["OrganizationName"];
	else
		$OrganizationName="";

	$Rest = "";
	$text="<br>Search Keywords : <br />";
	echo "Searched For: $SearchedFor";

	//Create SQL Search Statements from Form Data
	if($StartDate!="" & $EndDate!="")
	{
		$text.="Start Date: ".date("m/j/y", strtotime($StartDate))." to End Date: ".date("m/j/y", strtotime($EndDate));
		if($Rest=="")
			$Rest.="where w.WRKSPDATE Between '$StartDate' AND '$EndDate' ";
		else
			$Rest.="AND w.WRKSPDATE BETWEEN '$StartDate' AND '$EndDate' ";
	}
	if($WorkshopTopic!="")
	{
		$text.="Workshop Topic: $WorkshopTopic<br />";
		if($Rest=="")
			$Rest.="where wt.WRKSPTOPIC LIKE '%$WorkshopTopic%' ";
		else
			$Rest.="AND wt.WRKSPTOPIC LIKE '%$WorkshopTopic%' ";
	}
	if($PresenterFirst!="")
	{
		$text.="Presenter First Name: $PresenterFirst<br />";
		if($Rest=="")
			$Rest.="where pr.FIRSTNAME LIKE '%$PresenterFirst%' ";
		else
			$Rest.="AND pr.FIRSTNAME LIKE '%$PresenterFirst%' ";
	}
	if($PresenterLast!="")
	{
		$text.="Presenter Last Name: $PresenterLast<br />";
		if($Rest=="")
			$Rest.="where pr.LASTNAME LIKE '%$PresenterLast%' ";
		else
			$Rest.="AND pr.LASTNAME LIKE '%$PresenterLast%' ";
	}
	if($Location!="")
	{
		$text.="Location: $Location<br />";
		if($Rest=="")
			$Rest.="where w.LOCATION LIKE '%$Location%' ";
		else
			$Rest.="AND w.LOCATION LIKE '%$Location%' ";
	}
	if($WorkshopLanguage!="")
	{
		$text.="Workshop Language: $WorkshopLanguage<br />";
		if($Rest=="")
			$Rest.="where l.LANG LIKE '%$WorkshopLanguage%' ";
		else
			$Rest.="AND l.LANG LIKE '%$WorkshopLanguage%' ";
	}
	if($CommunityPartner!="")
	{
		$text.="Community Partner: $CommunityPartner<br />";
		if($Rest=="")
			$Rest.="where o.COMMUNITYPARTNER='$CommunityPartner' ";
		else
			$Rest.="AND o.COMMUNITYPARTNER='$CommunityPartner' ";
	}
	if($FName!="")
	{
		$text.="First Name: $FName<br />";
		if($Rest=="")
			$Rest.="where p.FIRSTNAME LIKE '%$FName%' ";

		else
			$Rest.="AND p.FIRSTNAME LIKE '%$FName%' ";
	}
	if($LName!="")
	{
		$text.="Last Name: $LName<br />";
		if($Rest=="")
			$Rest.="where p.LASTNAME LIKE '%$LName%' ";
		else
			$Rest.="AND p.LASTNAME LIKE '%$LName%' ";
	}
	if($TheCity!="")
	{
		$text.="City: $TheCity<br />";
		if($Rest=="")
			$Rest.="where p.CITY LIKE '%$TheCity%' ";
		else
			$Rest.="AND p.CITY LIKE '%$TheCity%' ";
	}
	if($PartType!="")
	{
		$text.="Participant Type: $PartType<br />";
		if($Rest=="")
			$Rest.="where t.TYPE='$PartType' ";
		else
			$Rest.="AND t.TYPE='$PartType' ";
	}
	if($Languages!="")
	{
		$text.="Languages: $Languages<br />";
		if($Rest=="")
			$Rest.="where l.LANG LIKE '%$Languages%' ";
		else
			$Rest.="AND l.LANG LIKE '%$Languages%' ";
	}

	if($Newsletter!="")
	{
		$text.="Newsletter: $Newsletter<br />";
		if($Rest=="")
			$Rest.="where p.SENDNEWSLETTER='$Newsletter' ";
		else
			$Rest.="AND p.SENDNEWSLETTER='$Newsletter' ";
	}
	if($Membership!="")
	{
		$text.="Membership: $Membership<br />";
		if($Rest=="")
			$Rest.="where p.SENDMEMBERINFO='$Membership' ";
		else
			$Rest.="AND p.SENDMEMBERINFO='$Membership' ";
	}
	if($OrganizationName!="")
	{
		$text.="Organization Name: $OrganizationName<br />";
		if($Rest=="")
			$Rest.="where o.ORGNAME LIKE '%$OrganizationName%' ";
		else
			$Rest.="AND o.ORGNAME LIKE '%$OrganizationName%' ";
	}

	if($SearchedFor == "Workshop")
	{
		if($Rest=="")
			$Rest.="where w.ORGID=o.ORGID AND w.WRKSPTOPICID=wt.WRKSPTOPICID AND w.PRESENTERID=pr.PRESENTERID AND
					l.LANGID = w.WRKSPLANGID";
  		else
  			$Rest.="AND w.ORGID=o.ORGID AND w.WRKSPTOPICID=wt.WRKSPTOPICID AND w.PRESENTERID=pr.PRESENTERID AND
					l.LANGID = w.WRKSPLANGID";
  		$sql="select w.WRKSPID, w.WRKSPDATE, w.WRKSPTIME, w.LOCATION, l.LANG, wt.WRKSPTOPIC, count(wp.PARTICIPANTID) as
			No_of_Participants, sum(wp.NUMBEROFCHILDREN) as No_of_Children,  w.CHILDRENPRESENT, w.ADDRESS, w.CITY, w.ZIPCODE,
			pr.FIRSTNAME as PRESENTERFIRST, pr.LASTNAME as PRESENTERLAST, o.ORGNAME, o.COMMUNITYPARTNER from  presenter pr,
			language l, workshoptopics wt, organization o, workshop w LEFT JOIN workshopparticipants wp ON
			(w.WRKSPID=wp.WRKSPID) $Rest group by w.WRKSPID ORDER BY w.WRKSPID ASC";
		$result=mysql_query($sql);
		$NoWorkshops=mysql_num_rows($result);
		echo "<div style='width:700px;overflow:auto;'>";
		echo "<table border='1'>";
		echo "	<tr>
					<th>Workshop ID</th>
					<th>Date</th>
					<th>Time</th>
					<th>Location</th>
					<th>Language</th>
					<th>Topic</th>
					<th>Number of Participants</th>
					<th>Number of Children</th>
					<th>Children Present</th>
					<th>Address</th>
					<th>City</th>
					<th>Zipcode</th>
					<th>Presenter First</th>
					<th>Presenter Last</th>
					<th>Organization Name</th>
					<th>Community Partner</th>
				</tr>";

		//keeps getting the next row until there are no more to get
		while($row = mysql_fetch_array($result))
		{
			// Print out the contents of each row into a table
			echo "<tr><td>";
			echo "<a href=\"EditWorkshop.php?id=".$row['WRKSPID']."\">".$row['WRKSPID']."</a>";
			echo "</td><td>";
			echo date("m/j/y", strtotime($row['WRKSPDATE']));
			echo "</td><td>";
			$hours=strval(substr($row['WRKSPTIME'],0,2));
			if($hours>12)
			{
				$hours=$hours-12;
				$tod="pm";
			}
			else
				$tod="am";
			$mins=strval(substr($row['WRKSPTIME'],3,2));
			echo $hours.":".$mins."".$tod;
			echo "</td><td>";
			echo $row['LOCATION'];
			echo "</td><td>";
			echo $row['LANG'];
			echo "</td><td>";
			echo $row['WRKSPTOPIC'];
			echo "</td><td>";
			echo $row['No_of_Participants'];
			echo "</td><td>";
			echo $row['No_of_Children'];
			echo "</td><td>";
			echo $row['CHILDRENPRESENT'];
			echo "</td><td>";
			echo $row['ADDRESS'];
			echo "</td><td>";
			echo $row['CITY'];
			echo "</td><td>";
			echo $row['ZIPCODE'];
			echo "</td><td>";
			echo $row['PRESENTERFIRST'];
			echo "</td><td>";
			echo $row['PRESENTERLAST'];
			echo "</td><td>";
			echo $row['ORGNAME'];
			echo "</td><td>";
			echo $row['COMMUNITYPARTNER'];
			echo "</td></tr>";

		}
		echo "</table></div><br/><br/>";
		echo "Total Number of Workshops: $NoWorkshops<br><br>";
		echo "<b>Number of Workshops Held by Each Organization</b><br>";
		$orgsql="select o.ORGNAME, count(w.WRKSPID) as No_of_Workshops from organization o, workshop w
			where o.ORGID=w.ORGID GROUP BY o.ORGID";
		$result=mysql_query($orgsql);
		while($row = mysql_fetch_array($result))
		{
			echo $row['ORGNAME'].": ".$row['No_of_Workshops']."<br>";
		}
	}
	else if($SearchedFor == "Participant")
	{
		$query_Language = "SELECT LANGID, LANG from language";
		$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
		$query_Ethnicity = "SELECT ETHNICITYID, ETHNICITY from ethnicity";
		$Ethnicity = mysql_query($query_Ethnicity, $db_conn) or die(mysql_error());
		$query_PartType = "SELECT TYPEID, TYPE from type";
		$Type = mysql_query($query_PartType, $db_conn) or die(mysql_error());
  		$participantQuery="select DISTINCT p.*";
		while ($row_Language = mysql_fetch_assoc($Language))
		{
			$participantQuery.=", if((count( if( l.LANG = '" .$row_Language['LANG']."', 1, NULL ) ))>0, 'Yes', '') AS ".$row_Language['LANG'];
		}

		while ($row_Type = mysql_fetch_assoc($Type))
		{
			$participantQuery.=", if((count( if( t.TYPE = '" .$row_Type['TYPE']."', 1, NULL ) ))>0, 'Yes', '') AS ".preg_replace("/[^a-zA-Z0-9s]/", "_", $row_Type['TYPE']);
		}

		while ($row_Ethnicity = mysql_fetch_assoc($Ethnicity))
		{
			$participantQuery.=", if((count( if( e.ETHNICITY = '" .$row_Ethnicity['ETHNICITY']."', 1, NULL ) ))>0, 'Yes', '') AS ".preg_replace("/[^a-zA-Z0-9s]/", "_", $row_Ethnicity['ETHNICITY']);
		}
		$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
		$Ethnicity = mysql_query($query_Ethnicity, $db_conn) or die(mysql_error());
		$Type = mysql_query($query_PartType, $db_conn) or die(mysql_error());
		$sql=  "$participantQuery
				FROM participants p
				LEFT JOIN workshopparticipants wp ON ( p.PARTICIPANTID = wp.PARTICIPANTID )
				LEFT JOIN workshop w ON ( wp.WRKSPID = w.WRKSPID )
				LEFT JOIN participanttype pt ON ( p.PARTICIPANTID = pt.PARTICIPANTID )
				LEFT JOIN TYPE t ON ( pt.TYPEID = t.TYPEID )
				LEFT JOIN participantlanguage pl ON ( pl.PARTICIPANTID = p.PARTICIPANTID )
				LEFT JOIN language l ON ( pl.LANGID = l.LANGID )
				LEFT JOIN participantethnicity pe ON ( pe.PARTICIPANTID = p.PARTICIPANTID )
				LEFT JOIN ethnicity e ON ( e.ETHNICITYID = pe.ETHNICITYID )
				$Rest group by p.PARTICIPANTID order by p.LASTNAME, p.FIRSTNAME;";

  		$result=mysql_query($sql);
		$NoParticipants=mysql_num_rows($result);
		echo "<div style='width:700px;overflow:auto;'>";
		echo "<table border='1'>";
		echo "<tr> 	<th>Participant ID</th>
					<th>First Name</th>
					<th>Last Name</th>
					<th>Address</th>
					<th>City</th>
					<th>Zipcode</th>
					<th>Phone #</th>
					<th>Email</th>
					<th>Gender</th>
					<th>Younger than 20</th>
					<th>Has Disabilities</th>
					<th>0-12 Months</th>
					<th>13-23 Months</th>
					<th>2-4 Years</th>
					<th>5-6 Years</th>
					<th>7-8 Years</th>
					<th>Over 8 Years</th>
					<th>Newsletter</th>
					<th>Membership</th>
					<th>Number of Children</th>";
		while ($row_Language = mysql_fetch_assoc($Language))
		{
			echo " <th>".$row_Language['LANG']."</th>";
		}
		while ($row_Type = mysql_fetch_assoc($Type))
		{
			echo " <th>".preg_replace("/[^a-zA-Z0-9s]/", "_", $row_Type['TYPE'])."</th>";
		}
		while ($row_Ethnicity = mysql_fetch_assoc($Ethnicity))
		{
			echo " <th>".preg_replace("/[^a-zA-Z0-9s]/", "_", $row_Ethnicity['ETHNICITY'])."</th>";
		}
		echo "	</tr>";
		//keeps getting the next row until there are no more to get
		while($row = mysql_fetch_array($result))
		{
			$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
			$Ethnicity = mysql_query($query_Ethnicity, $db_conn) or die(mysql_error());
			$Type = mysql_query($query_PartType, $db_conn) or die(mysql_error());
			// Print out the contents of each row into a table
			echo "<tr><td>";
			echo "<a href=\"EditParticipant.php?id=".$row['PARTICIPANTID']."\">".$row['PARTICIPANTID']."</a>";
			echo "</td><td>";
			echo $row['FIRSTNAME'];
			echo "</td><td>";
			echo $row['LASTNAME'];
			echo "</td><td>";
			echo $row['ADDRESS'];
			echo "</td><td>";
			echo $row['CITY'];
			echo "</td><td>";
			echo $row['ZIPCODE'];
			echo "</td><td>";
			echo preg_replace("/([0-9]{3})([0-9]{3})([0-9]{4})/", "($1) $2-$3", $row['PHONENO']);
			echo "</td><td>";
			echo $row['EMAIL'];
			echo "</td><td>";
			echo $row['GENDER'];
			echo "</td><td>";
			echo $row['YOUNGERTHAN20'];
			echo "</td><td>";
			echo $row['HASDISABILITIES'];
			echo "</td><td>";
			echo $row['MONTHS_0_12'];
			echo "</td><td>";
			echo $row['MONTHS_13_23'];
			echo "</td><td>";
			echo $row['YEARS_2_4'];
			echo "</td><td>";
			echo $row['YEARS_5_6'];
			echo "</td><td>";
			echo $row['YEARS_7_8'];
			echo "</td><td>";
			echo $row['YEARS_OVER_8'];
			echo "</td><td>";
			echo $row['SENDNEWSLETTER'];
			echo "</td><td>";
			echo $row['SENDMEMBERINFO'];
			echo "</td><td>";
			echo $row['NUMBEROFCHILDREN'];
			while ($row_Language = mysql_fetch_assoc($Language))
			{
				echo "</td><td>";
				echo $row[$row_Language['LANG']];
			}
			while ($row_Type = mysql_fetch_assoc($Type))
			{
				echo "</td><td>";
				echo $row[preg_replace("/[^a-zA-Z0-9s]/", "_", $row_Type['TYPE'])];
			}
			while ($row_Ethnicity = mysql_fetch_assoc($Ethnicity))
			{
				echo "</td><td>";
				echo $row[preg_replace("/[^a-zA-Z0-9s]/", "_", $row_Ethnicity['ETHNICITY'])];
			}
			echo "</td></tr>";
		}
		echo "</table></div><br/><br/>";
		echo "Number of Participants: $NoParticipants<br>";
	}
	else if($SearchedFor == "Presenter")
	{
		$query_Language = "SELECT LANGID, LANG from language";
		$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
		if($Rest=="")
			$Rest.="where pr.ORGID=o.ORGID";
		else
			$Rest.="AND pr.ORGID=o.ORGID";
		$presenterQuery="select pr.*, o.ORGNAME";
		while ($row_Language = mysql_fetch_assoc($Language))
		{
			$presenterQuery.=", if((count( if( l.LANG = '" .$row_Language['LANG']."', 1, NULL ) ))>0, 'Yes', '') AS ".$row_Language['LANG'];
		}
		mysql_free_result($Language);
		$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
		$sql="$presenterQuery
			from organization o, presenter pr LEFT JOIN presenterlanguage pl ON (pr.PRESENTERID = pl.PRESENTERID) LEFT JOIN language l ON (l.LANGID = pl.LANGID) $Rest GROUP BY pr.PRESENTERID";
		$result=mysql_query($sql);
		$NoPresenters=mysql_num_rows($result);
		echo "<div style='width:700px;overflow:auto;'>";
		echo "<table border='1'>";
		echo "	<tr><th>Presenter ID</th>
					<th>First Name</th>
					<th>Last Name</th>
					<th>Address</th>
					<th>City</th>
					<th>Zipcode</th>
					<th>Phone No</th>
					<th>Email</th>
					<th>Fax No</th>
					<th>Contract Trainer</th>
					<th>Organization Name</th>";
		while ($row_Language = mysql_fetch_assoc($Language))
		{
			echo " <th>".$row_Language['LANG']."</th>";
		}
		echo "	</tr>";
		//keeps getting the next row until there are no more to get
		while($row = mysql_fetch_array($result))
		{
			$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
			// Print out the contents of each row into a table
			echo "<tr><td>";
			echo "<a href=\"EditPresenter.php?pid=".$row['PRESENTERID']."\">".$row['PRESENTERID']."</a>";
			echo "</td><td>";
			echo $row['FIRSTNAME'];
			echo "</td><td>";
			echo $row['LASTNAME'];
			echo "</td><td>";
			echo $row['ADDRESS'];
			echo "</td><td>";
			echo $row['CITY'];
			echo "</td><td>";
			echo $row['ZIPCODE'];
			echo "</td><td>";
			echo preg_replace("/([0-9]{3})([0-9]{3})([0-9]{4})/", "($1) $2-$3", $row['PHONENO']);
			echo "</td><td>";
			echo $row['EMAIL'];
			echo "</td><td>";
			echo preg_replace("/([0-9]{3})([0-9]{3})([0-9]{4})/", "($1) $2-$3", $row['FAX_NO']);
			echo "</td><td>";
			echo $row['CONTRACTTRAINER'];
			echo "</td><td>";
			echo $row['ORGNAME'];
			while ($row_Language = mysql_fetch_assoc($Language))
			{
				echo "</td><td>";
				echo $row[$row_Language['LANG']];
			}
			echo "</td></tr>";
		}
		echo "</table></div><br/><br/>";
		echo "Number of Presenters: $NoPresenters<br>";
	}
	else if($SearchedFor == "Organization")
	{
		$sql="select o.* from organization o $Rest order by ORGNAME;";
		$result=mysql_query($sql);
		$NoOrganizations=mysql_num_rows($result);
  		echo "<div style='width:700px;overflow:auto;'>";
      	echo "<table border='1' >";
    	echo "	<tr><th>Organization ID</th>
					<th>Name</th>
					<th>Community Partner</th>
					<th>Address</th>
					<th>City</th>
					<th>Contact: First Name</th>
					<th>Last Name</th>
					<th>Title</th>
					<th>Email</th>
					<th>Phone</th>
					<th>Fax</th>
				</tr>";
    	//keeps getting the next row until there are no more to get
    	while($row = mysql_fetch_array($result))
    	{
    	  	// Print out the contents of each row into a table
    	  	echo "<tr><td>";
			echo "<a href=\"EditOrganization.php?id=".$row['ORGID']."\">".$row['ORGID']."</a>";
			echo "</td><td>";
    	  	echo $row['ORGNAME'];
    	  	echo "</td><td>";
    	  	echo $row['COMMUNITYPARTNER'];
    	  	echo "</td><td>";
    	  	echo $row['ADDRESS'];
    	  	echo "</td><td>";
    	  	echo $row['CITY'];
    	  	echo "</td><td>";
    	  	echo $row['CONTACTFIRSTNAME'];
    	  	echo "</td><td>";
    	  	echo $row['CONTACTLASTNAME'];
    	  	echo "</td><td>";
    	  	echo $row['CONTACTTITLE'];
     	  	echo "</td><td>";
    	  	echo $row['CONTACTEMAIL'];
    	  	echo "</td><td>";
    	  	echo preg_replace("/([0-9]{3})([0-9]{3})([0-9]{4})/", "($1) $2-$3", $row['CONTACTPHONE']);
     	  	echo "</td><td>";
    	  	echo preg_replace("/([0-9]{3})([0-9]{3})([0-9]{4})/", "($1) $2-$3", $row['CONTACTFAX']);
    	  	echo "</td></tr>";
    	}
      	echo "</table></div><br/><br/>";
  		echo "Number of Organizations: $NoOrganizations<br>";
	}
	else if($SearchedFor == "NoChildren")
	{
		if($Rest=="")
			$Rest.="where l.LANGID = w.WRKSPLANGID AND p.PARTICIPANTID = wp.PARTICIPANTID AND w.WRKSPID = wp.WRKSPID";
		else
			$Rest.="AND l.LANGID = w.WRKSPLANGID AND p.PARTICIPANTID = wp.PARTICIPANTID AND w.WRKSPID = wp.WRKSPID";
		$sql="select w. WRKSPID, w.WRKSPDATE, w.WRKSPTIME, w.LOCATION, l.LANG, SUM( wp.NUMBEROFCHILDREN ) AS TotalNoChildren
			FROM language l, participants p, workshop w, workshopparticipants wp $Rest GROUP BY w.WRKSPID;";
		$result=mysql_query($sql);
 		echo "<div style='width:700px;overflow:auto;'>";
        echo "<table border='1'>";
      	echo "	<tr><th>Workshop ID</th>
					<th>Workshop Date</th>
					<th>Workshop Time</th>
					<th>Workshop Location</th>
					<th>Workshop Language</th>
					<th>Number of Children</th>
				</tr>";
      	//keeps getting the next row until there are no more to get
      	while($row = mysql_fetch_array($result))
      	{
      	  	$SumNoChildren=$SumNoChildren + $row['TotalNoChildren'];
      	  	// Print out the contents of each row into a table
      	  	echo "<tr><td>";
			echo "<a href=\"EditWorkshop.php?id=".$row['WRKSPID']."\">".$row['WRKSPID']."</a>";
			echo "</td><td>";
      	  	echo date("m/j/y", strtotime($row['WRKSPDATE']));
      	  	echo "</td><td>";
      	  	$hours=strval(substr($row['WRKSPTIME'],0,2));
			if($hours>12)
			{
				$hours=$hours-12;
				$tod="pm";
			}
			else
				$tod="am";
			$mins=strval(substr($row['WRKSPTIME'],3,2));
			echo $hours.":".$mins."".$tod;
      	  	echo "</td><td>";
      	  	echo $row['LOCATION'];
			echo "</td><td>";
      	  	echo $row['LANG'];
      	  	echo "</td><td>";
      	  	echo $row['TotalNoChildren'];
      	  	echo "</td></tr>";
      	}
        echo "</table></div><br/><br/>";
  		echo "Total Number of Children From All Workshops in Date Range: $SumNoChildren<br>";
	}
	else if($SearchedFor == "LanguagesSpokenAtHome")
	{
		$query_Language = "SELECT LANGID, LANG from language";
		$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
		if($Rest=="")
			$Rest.="where wp.WRKSPID = w.WRKSPID AND wp.PARTICIPANTID = p.PARTICIPANTID	AND pl.PARTICIPANTID = p.PARTICIPANTID
					AND l.LANGID = pl.LANGID";
		else
			$Rest.="AND wp.WRKSPID = w.WRKSPID AND wp.PARTICIPANTID = p.PARTICIPANTID	AND pl.PARTICIPANTID = p.PARTICIPANTID
					AND l.LANGID = pl.LANGID";
		$languageQuery="select w.WRKSPID, w.WRKSPDATE, w.WRKSPTIME, w.LOCATION";
		while ($row_Language = mysql_fetch_assoc($Language))
		{
			$languageQuery.=", count( if( l.LANG = '" .$row_Language['LANG']."', 1, NULL ) ) AS ".$row_Language['LANG'];
		}
		mysql_free_result($Language);
		$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
		$sql="$languageQuery
			from workshopparticipants wp, workshop w, participants p, language l, participantlanguage pl
			$Rest
			group by w.WRKSPID";
		$result=mysql_query($sql);
		echo "<div style='width:700px;overflow:auto;'>";
		echo "<table border='1'>";
		echo "	<tr><th>Workshop ID</th>
					<th>Workshop Date</th>
					<th>Time</th>
					<th>Location</th>";
					while ($row_Language = mysql_fetch_assoc($Language))
					{
						echo " <th>".$row_Language['LANG']."</th>";
					}
		echo "</td></tr>";

		//keeps getting the next row until there are no more to get
		while($row = mysql_fetch_array($result))
		{
			$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
			while ($row_Language = mysql_fetch_assoc($Language))
			{
				$Total[$row_Language['LANG']]=$Total[$row_Language['LANG']] + $row[$row_Language['LANG']];
			}
			$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
			// Print out the contents of each row into a table
			echo "<tr><td>";
			echo "<a href=\"EditWorkshop.php?id=".$row['WRKSPID']."\">".$row['WRKSPID']."</a>";
			echo "</td><td>";
			echo date("m/j/y", strtotime($row['WRKSPDATE']));
			echo "</td><td>";
			$hours=strval(substr($row['WRKSPTIME'],0,2));
			if($hours>12)
			{
				$hours=$hours-12;
				$tod="pm";
			}
			else
				$tod="am";
			$mins=strval(substr($row['WRKSPTIME'],3,2));
			echo $hours.":".$mins."".$tod;
			echo "</td><td>";
			echo $row['LOCATION'];
			while ($row_Language = mysql_fetch_assoc($Language))
			{
				echo "</td><td>";
				echo $row[$row_Language['LANG']];
			}
			echo "</td></tr>";
		}
		echo "</table></div><br/><br/>";
		$Language = mysql_query($query_Language, $db_conn) or die(mysql_error());
		echo "Totals from all Workshops<br>";
		while ($row_Language = mysql_fetch_assoc($Language))
		{
			echo $row_Language['LANG']." = ".$Total[$row_Language['LANG']]."<br>";
		}
	}
	echo $text;
	mysql_close();
	ob_end_flush();
?>
<br>
<form method="post" action="Export.php">
<input type="submit" name="FileFormat" value="Export to Word" class="button">
<input type="submit" name="FileFormat" value="Export to Excel" class="button">
<input type="hidden" name="sqlQuery" value="<?php echo($sql) ?>">
</form>
</div>



<?php
echo $layout[1];
?>

</body>
</html>